PostgreSQL 数据库性能 Ctid 与 Rowid 比较

1 背景知识

1、针对Oracle数据库， Oracle 内核与原理 ROWID 可用于快速的数据访问。
2、对于 PostgreSQL，也有类似 Oracle 内核与原理 ROWID 的访问方式，称之为 citd。

3、但是PostgreSQL 由于自身MVCC实现机制的原因，会导致 citd 访问不到数据。

4、可以使用 currtid 函数基本可以满足 Oracle 内核与原理 ROWID 的功能，但自身又存在性能问题。

本文向大家介绍如何通过currtid 实现rowid 的功能，以及解决currtid 存在的性能问题。

1.1 Oracle Rowid

对于Oracle ，一条 tuple 的 Rowid 通常情况下是不会变化的。

💡 触发row movement（行迁移）的操作除外，如：跨分区迁移update，表shrink。

因此，Oracle 内核与原理 ROWID 记录了 tuple 的物理存储位置，通过 Rowid 可以非常快速地访问tuple。

因此，在极致性能的应用设计里，经常会使用到 Oracle 内核与原理 ROWID。典型的使用场景可以使用rowid快速修改某一行。

DECLARE
        CURSOR cur01 AS SELECT rowid FROM tab01;
BEGIN
        ......
        UPDATE tab1 WHERE rowid='xxx';
END;

1.2 PostgreSQL CTID

对于PostgreSQL，也有类似 Oracle 内核与原理 ROWID 的 citd，格式 (blockid,slotid)，同样记录了tuple存储的物理位置，通过 citd 也能快速的访问数据。

SELECT * FROM t01 WHERE ctid='(0,1)';

1.3 环境准备

DROP TABLE t01;
CREATE TABLE t01 (id int, name varchar(50)); 
INSERT INTO t01 VALUEStext);

2 使用CTID 地址访问数据

由于PostgreSQL的多版本（MVCC）机制， citd 会随update操作变化。

这种情况下，使用 citd 有可能因为tuple被update，导致访问不到数据。

2.1 A 用户

SELECT ctid FROM t01 WHERE id=1;
//屏幕输出：
返回 (0,1) 

2.2 B 用户

SELECT ctid FROM t01 WHERE id=1;
//屏幕输出：
返回 (0,1)

2.3 A 用户

UPDATE t01 SET name='aa' WHERE ctid='(0,1)';
SELECT ctid FROM t01 WHERE id=1;
//屏幕输出：
返回 (0,2) 

2.4 B 用户

SELECT * FROM t01 WHERE ctid='(0,1)';
//屏幕输出：
(0 行记录)

在有并发的情况下，用 citd 访问是不可靠的。
例子中，B用户通过 citd 访问时，就会发现找不到数据。

3 使用 currtid 函数，解决访问数据问题

PostgreSQL的update操作实际DELETE AND INSERT 的结合体。

对于 update 操作完成后，在 vacuum 之前，原始tuple是包含指向新tuple的 citd 。函数 currtid 可以通过旧 citd 取得 tuple 的最新版的 Ctid。具体见以下例子：

3.1 环境准备

TRUNCATE TABLE t01;
INSERT INTO t01 VALUES(1,'a');
INSERT 0 1

3.2 A 用户

SELECT CTID FROM t01 WHERE id=1;
//屏幕输出：
 ctid  
-------
 (0,1)
(1 row)

UPDATE t01 SET name='aa' WHERE id=1;
UPDATE 1
SELECT ctid FROM t01 WHERE id=1;
//屏幕输出：
 ctid  
-------
 (0,2)
(1 row)

3.3 B 用户

SELECT * FROM t01 WHERE ctid='(0,1)';
//屏幕输出：
 id | name 
----+------
(0 rows)

SELECT currtidregclass,'(0,1)';
//屏幕输出：
curritd 
---------
 (0,2)
(1 row)

SELECT * FROM t01 WHERE ctid=currtidregclass,'(0,1)';
//屏幕输出：
 id |   name    
----+-----------
  1 | aa       
(1 row)

可以看到，通过将初始的 citd 输入 currtid 函数中，可以取得最新的 citd。

currtid 有效的前提是update 后，多版本信息没有被清理掉，也就是没有进行vacuum操作。

4 使用curritd函数时，函数性能问题

4.1 查看 currtid 函数的执行计划

从以上例子可以看到，使用 currtid 可以部分避免期间数据被修改的问题而导致的通过Ctid无法读取到数据的问题。

但是，这里有个性能的问题。

4.2 currtid 函数的执行计划

EXPLAIN SELECT * 
FROM t01
WHERE ctid=currtidregclass,'(0,1)';
//屏幕输出：
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on t01  (cost=0.00..18.10 rows=1 width=122)
   Filter: (ctid = currtidoid, '(0,1)'::tid)

4.3 ctid 的执行计划

EXPLAIN SELECT * FROM t01 WHERE ctid='(0,2)';
//屏幕输出：
                     QUERY PLAN
-----------------------------------------------------
 Tid Scan on t01  (cost=0.00..4.01 rows=1 width=122)
   TID Cond: (ctid = 'tid

1、当使用 currtid 函数时，执行计划为 seq scan。
2、直接使用Ctid 回表时，执行计划为 Tid Scan。

这里可以抛出一个问题，为什么无法使用 Tid Scan ?

4.4 问题分析和总结

其实，这是由于函数的稳定态属性导致的。函数稳定态对于执行效率的影响，可以参照：PostgreSQL 数据库性能 函数稳定性影响函数调用次数

我们来看 currtid 函数属性：

SELECT proname,provolatile FROM pg_proc WHERE proname='currtid';
//屏幕输出：
 proname | provolatile
---------+-------------
 currtid | v
(1 行记录)

函数属性是 volatile ，volatile 函数导致无法使用 TID scan。

更多信息请参见：kingbase 函数的稳定性

4.5 currtid 函数属性修改为immutable

UPDATE pg_proc SET provolatile='i' WHERE proname='currtid';

4.6 查看 currtid 函数的执行计划

EXPLAIN SELECT * FROM t01 WHERE ctid=currtidregclass,'(0,1)';
//屏幕输出：
                     QUERY PLAN
-----------------------------------------------------
 Tid Scan on t01  (cost=0.00..4.01 rows=1 width=122)
   TID Cond: (ctid = 'tid
(2 行记录)

可以看到，修改函数的属性为 immutable后，可以走 Tid Scan了。

5 volatile 函数与 immutable函数差异

5.1 当 currtid 是volatile 类型的函数时：

1、优化器采取 Seq Scan，针对每个tuple，都会执行一次函数调用。

2、函数调用是在访问tuple之后，因此，能够保证数据的绝对准确性；

5.2 当 currtid 是immutable 类型的函数

1、针对整个查询，只需调用一次函数。
2、执行SQL时，先执行函数，再将结果以参数形式传给SQL。

3、如果从函数调用开始到SQL执行完成之前，如果tuple被update，可能导致返4、无论函数调用，还是TID scan，都是非常快的（微秒级别），基本可以避免影响。

5、当然，如果一定要考虑结果的绝对准确，修改函数稳定态属性不是安全的方法。

PS：以上的例子同时在 PostgreSQL12 和 PostgreSQL 进行过验证。